from random import choice
from docx import Document
from docx.enum.style import WD_STYLE_TYPE
from docx.enum.table import WD_TABLE_ALIGNMENT
from docx.enum.table import WD_CELL_VERTICAL_ALIGNMENT
from docx.shared import Cm
from openpyxl import load_workbook
from docx.shared import Pt
from docx.oxml.ns import qn

import docx
from decimal import Decimal
from random import choice
from docx import Document
from docx.enum.style import WD_STYLE_TYPE
from docx.enum.table import WD_TABLE_ALIGNMENT
from docx.enum.table import WD_CELL_VERTICAL_ALIGNMENT
from docx.shared import Cm
from openpyxl import load_workbook
from docx.shared import Pt
from docx.oxml.ns import qn
from docx.shared import Cm, Inches, Pt # 导入单位换算函数
from docx.enum.section import WD_ORIENTATION
import re
from docxcompose.composer import Composer

def xlsx2docx1(fn,fn2):
    wb = load_workbook(fn)
    document = Document()
    document.styles['Normal'].font.name = u'宋体'
    document.styles['Normal']._element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')
    document.styles['Normal'].font.size = Pt(12)
    document.add_paragraph('    地块{0}由点号J1-J{1},J1围成')
    document.add_paragraph('    占地面积：P={:.2f}平方米，合{}亩。')
    table_styles = ['Table Grid']




    for ws in wb.worksheets:
        section = document.sections[0]
        section.page_width = Cm(21)  # 设置A4纸的宽度
        section.page_height = Cm(29.7)  # 设置A4纸的高度
        # 创建表头
        rows = list(ws.rows)
        #document.add_paragraph('地块'+ws.title[5]+'由点号')
        document.add_paragraph('')
        document.add_paragraph('    地块{0}由点号J1-J{1},J1围成'.format(ws.title[5],len(rows)-1))
        document.add_paragraph('    占地面积：P={:.2f}平方米，合{}亩。'.format(float(ws.cell(2,4).value),ws.cell(2,5).value))
        table = document.add_table(rows=len(rows),
                                   cols=len(rows[0])-2,
                                   style=choice(table_styles))

        for table_col in range(1,4):
            table.cell(0,table_col-1).text = str(ws.cell(1,table_col).value)
        for table_row in range(2,len(rows)+1):
            for table_col in range(2,4):
                table.cell(table_row-1,table_col-1).text = format(ws.cell(table_row,table_col).value, '.3f')
        for table_row in range(2, len(rows) + 1):
            table.cell(table_row - 1, 0).text = str(ws.cell(table_row,1).value)
        for table_row in range(0,len(rows)):
            for table_col in range(0,3):
                # 垂直居中
                table.cell(table_row,table_col).paragraphs[0].alignment = WD_TABLE_ALIGNMENT.CENTER
                table.cell(table_row,table_col).vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.CENTER
        for row in table.rows:
            # 设置行高
            row.height = Cm(1)

    wb2 = load_workbook(fn2,data_only=False)

    # document = Document()


    document.add_paragraph(' ')
    document.add_paragraph('土地分类面积表')
    # table_styles = ['Table Grid']

    section = document.sections[-1]
    section.start_type
    # section = document.add_section(WD_SECTION.ODD_PAGE)
    # section.start_type
    section.page_width = Cm(21)  # 设置A4纸的宽度
    section.page_height = Cm(29.7)  # 设置A4纸的高度

    section.orientation = WD_ORIENTATION.LANDSCAPE
    new_width, new_height = section.page_height, section.page_width
    section.page_width = new_width
    section.page_height = new_height

    # for ws in wb.worksheets:
    ws2 = wb2.worksheets[0]
    rows = list(ws2.rows)
    table2 = document.add_table(rows=len(rows),
                               cols=len(rows[0]),
                               style=choice(table_styles))

    for irow, row in enumerate(rows):
        for icol, col in enumerate(row):
            table2.cell(irow, icol).text = str(col.value)
            # None全部转为0
            if table2.cell(irow, icol).text == 'None':
                table2.cell(irow, icol).text = '0'
    table2.cell(1, 21).text = ' '
    table2.cell(2, 21).text = ' '

    '''
    整数保留整数，小数保留两位小数
    删除会变成整数保留整数，小数保留三位小数
    也可改成全部保留两位小数
    '''
    for jrow in range(3, len(rows)):
        for jcol in range(1, len(rows[0])):
            table2.cell(len(rows) - 2, 0).text = ' '
            if table2.cell(jrow, jcol).text != '0':
                if re.findall(r'(?=.{3})\.', (table2.cell(jrow, jcol).text)) == ['.']:
                    table2.cell(jrow, jcol).text = str("{:.2f}".format(float(table2.cell(jrow, jcol).text)))
            else:
                table2.cell(jrow, jcol).text = ' '
            # if re.findall(r'(?=.{3})\.',(table.cell(irow, icol).text)) == '.':
            #     table.cell(irow, icol).text = str("{:.2f}".format(int(table.cell(jrow,jcol).text))).text
            #     print(str("{:.2f}".format(int(table.cell(jrow, jcol).text))).text)
            #     print(1)

    table2.cell(0, 0).merge(table2.cell(2, 0)).text = table2.cell(0, 0).text
    table2.cell(0, 1).merge(table2.cell(0, 10)).text = table2.cell(0, 1).text
    table2.cell(0, 11).merge(table2.cell(0, 19)).text = table2.cell(0, 11).text
    table2.cell(1, 1).merge(table2.cell(1, 3)).text = table2.cell(1, 1).text
    table2.cell(1, 6).merge(table2.cell(1, 10)).text = table2.cell(1, 6).text
    table2.cell(1, 11).merge(table2.cell(1, 12)).text = table2.cell(1, 11).text
    table2.cell(1, 14).merge(table2.cell(1, 16)).text = table2.cell(1, 14).text
    # table.cell(0,0).paragraphs[0].font.size=Pt(21)
    # row = table.rows
    # row.height = Cm(10)
    # table.columns[19].cells.width = Cm(1.85)
    # table.columns[20].cells.width = Cm(1.85)
    # table.columns[21].cells.width = Cm(1.85)
    table2.width = Cm(44)

    for cell in table2.columns[9].cells:
        cell.width = Cm(1.85)
    for cell in table2.columns[13].cells:
        cell.width = Cm(1.85)
    for cell in table2.columns[17].cells:
        cell.width = Cm(1.85)
    for cell in table2.columns[18].cells:
        cell.width = Cm(1.85)
    for cell in table2.columns[19].cells:
        cell.width = Cm(1.85)
    for cell in table2.columns[20].cells:
        cell.width = Cm(1.85)

    for table_row in range(0, len(rows)):
        for table_col in range(0, 22):
            # 垂直居中
            table2.cell(table_row, table_col).paragraphs[0].alignment = WD_TABLE_ALIGNMENT.CENTER
            table2.cell(table_row, table_col).vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.CENTER
    for row in table2.rows:
        # 设置行高
        row.height = Cm(0.65)


    document.styles['Normal'].font.name = u'宋体'
    document.styles['Normal']._element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')
    document.styles['Normal'].font.size = Pt(10)

    document.paragraphs[-2].runs[0].add_break(docx.enum.text.WD_BREAK.PAGE)
    # document.save(fn[:-4] + 'docx')

    document.save('report.docx')
    #document.save('report.docx')
xlsx2docx1('test.xlsx','test2.xlsx')

# from docx import Document
#
# def main(files,final_docx):
#     new_document = Document()
#     composer = Composer(new_document)
#     for fn in files:
#         composer.append(Document(fn))
#     composer.save(final_docx)
#
# main(["report1.docx","report2.docx"],"result.docx")